package com.house.excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.house.pojo.Houseuser;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelToList { 

 
    //使用POI读入excel工作簿文件 
    public static List<Houseuser> readWorkBook(InputStream inp) throws Exception { 
        // poi读取excel 
  
        //根据上述创建的输入流 创建工作簿对象 
        Workbook wb = WorkbookFactory.create(inp);      
        //页Sheet是从0开始索引的 
        Sheet sheet = wb.getSheetAt(0); 
        
        //得到Excel的行数
        int totalRow = sheet.getPhysicalNumberOfRows();
        //得到Excel的列数
            
        	int totalCell = sheet.getRow(0).getPhysicalNumberOfCells();
       
        //封装每个行的对象
        List<Houseuser> list = new ArrayList<Houseuser>();
        
        Houseuser houseuser;
        //利用foreach循环 遍历sheet中的所有行 
        for (int r=1;r<totalRow;r++) {
        	
        	Row row = sheet.getRow(r);
        	
        	houseuser =new Houseuser();
            //遍历row中的所有方格 
       	 	for (int c=0;c<totalCell;c++) { 
       	 		//输出方格中的内容，以空格间隔                 
       	 		Cell cell = row.getCell(c);
       	 		
       	 		if(c==0){
       	 			cell.setCellType(Cell.CELL_TYPE_STRING);
       	 			houseuser.setUserName(cell.getStringCellValue());
       	 		}
       	 		else if(c==1){
       	 			cell.setCellType(Cell.CELL_TYPE_STRING);
       	 			houseuser.setUserId(cell.getStringCellValue().toString());
       	 		}
       	 		else if(c==2){
       	 			cell.setCellType(Cell.CELL_TYPE_STRING);
       	 			houseuser.setUserHousenum(Integer.parseInt(cell.getStringCellValue()));
       	 		}
       	 		else if(c==3){
       	 			cell.setCellType(Cell.CELL_TYPE_STRING);
       	 			houseuser.setUserAddress(cell.getStringCellValue().toString());
       	 		}
       	 		else if(c==4){
       	 			cell.setCellType(Cell.CELL_TYPE_STRING);
       	 			houseuser.setUserHousekind(Integer.parseInt(cell.getStringCellValue()));
       	 		}
       	 		
       	 	}
       	 list.add(houseuser);
        //每一个行输出之后换行  
    	 System.out.println(); 
        } 
        //关闭输入流 
        inp.close(); 
        
        return list;
    } 
 
}
